package soccer.model.player;

import java.util.List;

import soccer.model.team.Team;
import soccer.utility.db.dbUtility;


public class PlayerDAOImpl implements PlayerDAO {
       public  List searchPlayerByName(String name , String surname){
    	   List playerList = null;
    	   StringBuffer query = new StringBuffer();
    	   query.append("SELECT * FROM ");
    	   query.append(" team AS t RIGHT JOIN player AS p ON t.teamKodu = p.teamId ");
    	   query.append(" WHERE p.name = ? and p.surname = ? ");
    	   
    	   Object[] params =  new Object[]{name , surname};
    	   dbUtility db = dbUtility.getInstance();
    	   playerList = db.execute(query.toString(), params);
    	   return playerList;
       }
       
       public  List searchPlayerByLicenceNo(int licenceNo){
    	   List playerList = null;
    	   StringBuffer query = new StringBuffer();
    	   query.append("SELECT * FROM ");
    	   query.append(" team as t RIGHT JOIN player AS p ON  t.teamKodu = p.teamId ");
    	   query.append(" WHERE licenceNo = ?");
    	   
    	   Object[] params =  new Object[]{licenceNo};
    	   dbUtility db = dbUtility.getInstance();
    	   playerList = db.execute(query.toString(), params);
    	   return playerList;
       }
       public  List searchPlayerByTeam(int teamCode){
    	   List playerList = null;
    	   StringBuffer query = new StringBuffer();
    	  
    	   query.append("SELECT * FROM ");
    	   query.append(" team AS t RIGHT JOIN player AS p ON t.teamKodu = p.teamId ");
    	   query.append(" WHERE p.teamId = ? ");
    	   
    	   Object[] params =  new Object[]{teamCode};
    	   dbUtility db = dbUtility.getInstance();
    	   playerList = db.execute(query.toString(), params);
    	   return playerList;
       }
       public List getMatchOfPlayerPlayed(Player p){
    	    List playerPlayedMatch = null;
    	    StringBuffer query = new StringBuffer();
    	    query.append("SELECT m.matchDate , t1.teamName AS home ,md.homeTeamSkor , md.awayTeamSkor ,t2.teamName AS away");
    	    query.append(" FROM player p , playersofmatch pom , matchDetail md ,matches m ,team t1 ,team t2 ");
    	    query.append(" WHERE p.playerId = pom.playerId AND md.matchDetailId = pom.matchDetailId");
    	    query.append(" AND md.matchId = m.matchId AND t2.teamKodu = m.awayTeamId AND t1.teamKodu = m.homeTeamId ");
    	    query.append(" AND p.name = ? AND p.surname = ?");
    	    
    	    Object[] params = new Object[]{p.getName(),p.getSurname()};
    	    dbUtility db = dbUtility.getInstance();
    	    playerPlayedMatch = db.execute(query.toString(), params);
    	    return playerPlayedMatch;
       }
       public int enterPlayer(Player p){
    	   StringBuffer query =  new StringBuffer();
    	   query.append("INSERT INTO player ");
    	   query.append("( name ,surname ,birthdate ,birthplace," );
    	   query.append("licenceNo)");
    	   query.append(" VALUES (?,?,?,?,?)");
    	   
    	   Object[] params =  new Object[]{p.getName(),p.getSurname()
    			   ,p.getBirthDate(),p.getBirthPlace(),p.getLicenceNo()};
    	   dbUtility db =  dbUtility.getInstance();
    	   int rows = 0;
    	   rows = db.update(query.toString(), params);
    	   return rows;
    	   }
       public int insertPlayerWithTeam(Player p,int teamCode){
    	   StringBuffer query = new StringBuffer();
    	   
    	   query.append("INSERT INTO player");
    	   query.append(" (name , surname , birthdate , birthPlace ,licenceNo");
    	   query.append("  ,beginDateToLicence ,finishDateToLicence , teamId)");
    	   query.append(" VALUES (?,?,?,?,?,?,?,? )");
    	   
           Object [] params = new Object[]{
        		   p.getName(),p.getSurname(),p.getBirthDate(),p.getBirthPlace(),
        		   p.getLicenceNo(),p.getBeginLicence(),p.getFinishLicence(),teamCode};
           
            dbUtility db = dbUtility.getInstance();
            int rows = db.update(query.toString(), params);
            return rows;
       }
       public List getPlayerHaveGoals(Player p){
    	   StringBuffer query = new StringBuffer();
   	    query.append(" SELECT m.matchDate , t1.teamName AS home ,md.homeTeamSkor , md.awayTeamSkor ,t2.teamName AS away,golminute");
   	    query.append(" FROM player p , playersofmatch pom , matchDetail md ,matches m ,team t1 ,team t2,goal g ");
   	    query.append(" WHERE p.playerId = pom.playerId AND md.matchDetailId = pom.matchDetailId");
   	    query.append(" AND md.matchId = m.matchId AND t2.teamKodu = m.awayTeamId AND t1.teamKodu = m.homeTeamId ");
   	    query.append(" AND p.name = ? AND p.surname = ?");
   	    query.append(" AND  g.golScorer = p.playerId AND  g.matchDetailId = md.matchDetailId ");
   	    query.append(" AND  g.matchDetailId = md.matchDetailId");
   	    Object[] params = new Object[]{p.getName(),p.getSurname()};
   	    dbUtility db = dbUtility.getInstance();
   	    List playerHaveGoalMatch = db.execute(query.toString(), params);
   	    return playerHaveGoalMatch;
       }
      }

